package it.sarasini.dmp;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class MusicDatabaseAdapter {
	
	/*
	 * ATTENZIONE
	 * 
	 * i vari campi vanno puliti prima di essere inseriti nel db
	 * 
	 */
	
	private Context context;
	private SQLiteDatabase db;
	private MusicOpenHelper dbHelper;

	public MusicDatabaseAdapter(Context context) {
		
		this.context = context;
	}
	
	public boolean openDatabase() throws SQLException {
		
		dbHelper = new MusicOpenHelper(context);
		
		db = dbHelper.getWritableDatabase();
		
		if(db == null)
			return false;
		
		return true;
	}

	public void close() {
		
		dbHelper.close();
	}
	
	public boolean addNewFile(String path, String artistName, String title, int lenght_ms) {
		
		path = DatabaseUtils.sqlEscapeString(path);
		title = DatabaseUtils.sqlEscapeString(title);
		artistName = DatabaseUtils.sqlEscapeString(artistName);
		
		artistName = replaceIllegalCharacters(artistName);
		title = replaceIllegalCharacters(title);
		
		DebugLog.log("addNewFile "+ title +","+ artistName +","+ path);
		
		// se il file è già presente esco
		if(getFileId(path) != -1) {
		
			DebugLog.log("Il file è già presente");
			return true;
		}
		
		// controllo se c'è già l'artista
		int artistId = getArtistId(artistName);
		
		// se non c'è lo inserisco
		if(artistId == -1) {
			
			DebugLog.log("Inserisco l'artista");
			
			ContentValues artistValues = new ContentValues();
			artistValues.put("name", artistName);
			
			artistId = (int) db.insert(MusicOpenHelper.ARTIST_TABLE_NAME, null, artistValues);
		}
		
		if(artistId == -1)
			return false;
		
		// inserisco il file
		ContentValues fileValues = new ContentValues();
		fileValues.put("path", path);
		fileValues.put("artist_id", artistId);
		fileValues.put("title", title);
		fileValues.put("lenght_ms", lenght_ms);
		
		if(db.insert(MusicOpenHelper.FILE_TABLE_NAME, null, fileValues) == -1)
			return false;
		
		return true;
	}
	
	public int getFileId(String path) {
		
		String[] columns = { "_id" };
		String selection = "path = ?";
		String[] selectionArgs = { path };
		
		Cursor c = db.query(MusicOpenHelper.FILE_TABLE_NAME, columns, selection, selectionArgs, null, null, null);
		
		if(c.getCount() == 0) 
			return -1;
		
		c.moveToFirst();
		
		return c.getInt(c.getColumnIndex("_id"));
	}
	
	public int getArtistId(String artistName) {
		
		String[] columns = { "_id" };
		String selection = "name = ?";
		String[] selectionArgs = { artistName };
		
		Cursor c = db.query(MusicOpenHelper.ARTIST_TABLE_NAME, columns, selection, selectionArgs, null, null, null);
		
		if(c.getCount() == 0) 
			return -1;
		
		c.moveToFirst();
		
		return c.getInt(c.getColumnIndex("_id"));
	}

	public ArrayList<ArtistData> getArtists() {
		
		ArrayList<ArtistData> artists = new ArrayList<ArtistData>();
		
		String[] columns = { "_id", "name" };
		
		Cursor c = db.query(MusicOpenHelper.ARTIST_TABLE_NAME, columns, null, null, null, null, null);
		
		if(c.getCount() == 0)
			return null;
		
		c.moveToFirst();
		for(int i=0; i<c.getCount(); i++, c.moveToNext())
			artists.add(new ArtistData(c.getInt(c.getColumnIndex("_id")), c.getString(c.getColumnIndex("name")).replace("\'", "")));
		
		return artists;
	}
	
	public ArrayList<FileData> getFilesFromArtistId(int artistId) {
		
		ArrayList<FileData> files = new ArrayList<FileData>();
		
		String[] columns = { "_id", "artist_id", "title", "path", "lenght_ms"};
		String selection = "artist_id = ?";
		String[] selectionArgs = { (new Integer(artistId)).toString() };
		
		Cursor c = db.query(MusicOpenHelper.FILE_TABLE_NAME, columns, selection, selectionArgs, null, null, null);
		
		if(c.getCount() == 0)
			return null;
		
		c.moveToFirst();
		for(int i=0; i<c.getCount(); i++, c.moveToNext())
			files.add(new FileData(
								c.getInt(c.getColumnIndex("_id")), 
								c.getInt(c.getColumnIndex("artist_id")), 
								c.getString(c.getColumnIndex("path")).replace("\'", ""),
								c.getString(c.getColumnIndex("title")).replace("\'", ""),
								c.getInt(c.getColumnIndex("lenght_ms"))
								));
		
		return files;
	}
	
	public ArrayList<FileData> getFiles() {
		
		ArrayList<FileData> files = new ArrayList<FileData>();
		
		String[] columns = { "_id", "artist_id", "title", "path", "lenght_ms"};
		
		Cursor c = db.query(MusicOpenHelper.FILE_TABLE_NAME, columns, null, null, null, null, null);
		
		if(c.getCount() == 0)
			return null;
		
		c.moveToFirst();
		for(int i=0; i<c.getCount(); i++, c.moveToNext())
			files.add(new FileData(
								c.getInt(c.getColumnIndex("_id")), 
								c.getInt(c.getColumnIndex("artist_id")), 
								c.getString(c.getColumnIndex("path")),
								c.getString(c.getColumnIndex("title")),
								c.getInt(c.getColumnIndex("lenght_ms"))
								));
		
		return files;
	}
	
	public String replaceIllegalCharacters(String str) {
		
		str = str.replace("\\", "");
		str = str.replace("/", "");
		str = str.replace("#", "");
		str = str.replace("°", "");
		str = str.replace("*", "");
		
		return str;
		
	}
	
}
